/**
 * Copyright (c) 2020 Coder League
 * All rights reserved.
 *
 * File：SupplierDaoImpl.java
 * History:
 *         2020年12月27日: Initially created, Chrise.
 */
package club.coderleague.cfss.dao.impl;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import club.coderleague.cfss.dao.extension.SupplierDaoExtension;
import club.coderleague.cfss.domain.entities.Suppliers;
import club.coderleague.cfss.domain.enums.EntityState;
import club.coderleague.cfss.util.CommonUtil;
import club.coderleague.data.jpa.domain.Page;
import club.coderleague.data.jpa.id.support.SnowflakeV4;
import club.coderleague.data.jpa.repository.support.AbstractDataRepositoryExtension;

/**
 * 供应商数据访问对象实现。
 * @author Chrise
 */
public class SupplierDaoImpl extends AbstractDataRepositoryExtension<Suppliers, Long, SnowflakeV4> implements SupplierDaoExtension {
	/**
	 * @see club.coderleague.cfss.dao.extension.SupplierDaoExtension#existsSupplier(java.lang.String, java.lang.String, java.lang.Long)
	 */
	@Override
	public boolean existsSupplier(String entname, String buslicenseno, Long entityid) {
		String sql = "select count(entityid) from suppliers where entitystate in (:entitystate) "
			+ "and (entname = :entname or buslicenseno = :buslicenseno)";
		Map<String, Object> params = new HashMap<>();
		params.put("entitystate", Arrays.asList(EntityState.APPLY.getValue(), EntityState.AUDITING.getValue(), EntityState.OPENING.getValue()));
		params.put("entname", entname);
		params.put("buslicenseno", buslicenseno);
		if (entityid != null) {
			sql += " and entityid != :entityid";
			params.put("entityid", entityid);
		}
		
		return (super.queryLongBySql(sql, params) > 0);
	}
	
	/**
	 * @see club.coderleague.cfss.dao.extension.SupplierDaoExtension#querySupplierPageData(java.util.List, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.Integer, java.lang.Integer)
	 */
	@Override
	public Page<Map<String, Object>> querySupplierPageData(List<Integer> states, String keyword, 
		String buslicense, String folicense, String testreport, String qualityreport, Integer pageIndex, Integer pageSize) {
		Map<String, Object> params = new HashMap<>();
		
		String csql = "select count(s.entityid) from Suppliers s " 
			+ "left join Areas p on s.provinceid = p.entityid " 
			+ "left join Areas c on s.cityid = c.entityid " 
			+ "left join Areas d on s.districtid = d.entityid " 
			+ "left join Areas r on s.streetid = r.entityid " 
			+ "left join Users u on s.auditor = u.entityid where s.entitystate in (:entitystate)";
		String qsql = "select s.entityid, s.entname, s.buslicenseno, concat('" + buslicense + "', s.buslicense) as buslicense, s.leader, s.contactnumber, " 
			+ "p.areaname as province, c.areaname as city, d.areaname as district, r.areaname as street, s.detailaddr, " 
			+ "concat('" + folicense + "', s.folicense) as folicense, " 
			+ "(case when s.testreport is null then '' else concat('" + testreport + "', s.testreport) end) as testreport, " 
			+ "(case when s.qualityreport is null then '' else concat('" + qualityreport + "', s.qualityreport) end) as qualityreport, " 
			+ "s.applytime, u.username as auditor, s.audittime, s.entitystate from Suppliers s " 
			+ "left join Areas p on s.provinceid = p.entityid " 
			+ "left join Areas c on s.cityid = c.entityid " 
			+ "left join Areas d on s.districtid = d.entityid " 
			+ "left join Areas r on s.streetid = r.entityid " 
			+ "left join Users u on s.auditor = u.entityid where s.entitystate in (:entitystate)";
		params.put("entitystate", states);
		
		if (CommonUtil.isNotEmpty(keyword)) {
			csql += " and (s.entname like :keyword or s.buslicenseno like :keyword or s.leader like :keyword or s.contactnumber like :keyword)";
			qsql += " and (s.entname like :keyword or s.buslicenseno like :keyword or s.leader like :keyword or s.contactnumber like :keyword)";
			params.put("keyword", "%" + keyword + "%");
		}
		
		qsql += " order by s.entitystate";
		
		return super.queryMapPageBySql(csql, qsql, pageIndex, pageSize, params);
	}
	
	/**
	 * @see club.coderleague.cfss.dao.extension.SupplierDaoExtension#querySupplierDetail(long, java.lang.String, java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public Map<String, Object> querySupplierDetail(long entityid, String buslicense, String folicense, String testreport, String qualityreport) {
		String sql = "select s.entname, s.buslicenseno, concat('" + buslicense + "', s.buslicense) as buslicense, s.leader, s.contactnumber, " 
			+ "concat(p.areaname, ' ', c.areaname, ' ', d.areaname, ' ', r.areaname) as areaname, s.detailaddr, " 
			+ "concat('" + folicense + "', s.folicense) as folicense, " 
			+ "(case when s.testreport is null then '' else concat('" + testreport + "', s.testreport) end) as testreport, " 
			+ "(case when s.qualityreport is null then '' else concat('" + qualityreport + "', s.qualityreport) end) as qualityreport from Suppliers s " 
			+ "left join Areas p on s.provinceid = p.entityid " 
			+ "left join Areas c on s.cityid = c.entityid " 
			+ "left join Areas d on s.districtid = d.entityid " 
			+ "left join Areas r on s.streetid = r.entityid where s.entityid = ?0";
		return super.queryMapBySql(sql, entityid);
	}
	
	/**
	 * @see club.coderleague.cfss.dao.extension.SupplierDaoExtension#querySupplierState(long)
	 */
	@Override
	public Integer querySupplierState(long supplier) {
		String jpql = "select entitystate from Suppliers where entityid = ?0";
		return super.queryInteger(jpql, supplier);
	}
	
	/**
	 * @see club.coderleague.cfss.dao.extension.SupplierDaoExtension#querySupplierName(long)
	 */
	@Override
	public String querySupplierName(long supplier) {
		String jpql = "select entname from Suppliers where entityid = ?0";
		return super.queryString(jpql, supplier);
	}
	
	/**
	 * @see club.coderleague.cfss.dao.extension.SupplierDaoExtension#queryPersonalInfo(long)
	 */
	@Override
	public Map<String, Object> queryPersonalInfo(long supplier) {
		String sql = "select s.entitystate from Suppliers s where s.entityid = ?0";
		return super.queryMapBySql(sql, supplier);
	}
	
	/**
	 * @see club.coderleague.cfss.dao.extension.SupplierDaoExtension#querySupplierData(long, java.lang.String, java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public Map<String, Object> querySupplierData(long supplier, String buslicense, String folicense, String testreport, String qualityreport) {
		String sql = "select s.entname, s.buslicenseno, concat('" + buslicense + "', s.buslicense) as buslicense, s.leader, s.contactnumber, " 
			+ "s.provinceid, pn.areaname as pareaname, s.cityid, cn.areaname as careaname, s.districtid, dn.areaname as dareaname, s.streetid, sn.areaname as sareaname, " 
			+ "s.detailaddr, concat('" + folicense + "', s.folicense) as folicense, " 
			+ "(case when s.testreport is null then null else concat('" + testreport + "', s.testreport) end) as testreport, " 
			+ "(case when s.qualityreport is null then null else concat('" + qualityreport + "', s.qualityreport) end) as qualityreport " 
			+ "from Suppliers s, Areas pn, Areas cn, Areas dn, Areas sn " 
			+ "where s.provinceid = pn.entityid and s.cityid = cn.entityid and s.districtid = dn.entityid and s.streetid = sn.entityid and s.entityid = ?0";
		return super.queryMapBySql(sql, supplier);
	}
}
